package com.zl.autocode.common.utils.ddl;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.zl.autocode.common.utils.generator.ReaderSql;
import com.zl.autocode.common.utils.generator.model.ConvertColumn;
import com.zl.autocode.common.utils.generator.model.ConvertConfig;
import com.zl.autocode.common.utils.generator.model.ConvertTable;
import com.zl.common.utils.FreemarkerTool;

/**
 * 建表语句处理
 * @author zhouliang
 *
 */
public class DdlUtil {
    private static final Logger LOGGER = LoggerFactory.getLogger(DdlUtil.class);
    private static Map<String,String> convertMap = new HashMap<>();
    private static Map<String,List<String>> keyMap = new HashMap<>();
    private static Map<String,List<String>> tblCommentMap = new HashMap<>();
    private static Map<String,List<String>> colCommentMap = new HashMap<>();
    static{
        convertMap.put("char", "char(%s)");
        convertMap.put("varchar", "varchar(%s)");
        convertMap.put("int", "integer");
        convertMap.put("timestamp", "timestamp");
        convertMap.put("number", "numeric(%s,%s)");
        convertMap.put("numeric", "numeric(%s,%s)");
        convertMap.put("clob", "text");
        convertMap.put("text", "text");
        convertMap.put("--", "varchar(%s)");
        
        //oceanbase, gauss, kingbase, db2, sybase, sqlite, mariadb, hsqldb, h2, phoenix, herddb, oracle9i, as400, informix, informix-sqli, derby
        //, edb, oscar, clickhouse, highgo, xugu, impala, firebirdsql, hive2, dm, oracle, mysql, sqlserver, postgresql
        
        keyMap.put(DdlConfigEnum.KEY_TYPE_1.getCode(), Arrays.asList("mysql,sqlserver".split(",")));//主键在create无名称
        keyMap.put(DdlConfigEnum.KEY_TYPE_2.getCode(), Arrays.asList("oracle,dm,postgresql,oceanbase".split(",")));//主键在create有名称
        
        tblCommentMap.put(DdlConfigEnum.TBL_COMMENT_TYPE_1.getCode(), Arrays.asList("mysql,oceanbase".split(",")));//表注释在create后
        tblCommentMap.put(DdlConfigEnum.TBL_COMMENT_TYPE_2.getCode(), Arrays.asList("oracle,dm,postgresql".split(",")));//表注释单独
        
        colCommentMap.put(DdlConfigEnum.COL_COMMENT_TYPE_1.getCode(), Arrays.asList("mysql,oceanbase".split(",")));//字段注释在create后
        colCommentMap.put(DdlConfigEnum.COL_COMMENT_TYPE_2.getCode(), Arrays.asList("oracle,dm,postgresql".split(",")));//字段注释单独
    }
    
    public static List<String> convertString(DdlConfig ddlConfig) throws Exception{
        freemarker.template.Configuration cfg = FreemarkerTool.initFreemarker(null);
        List<String> list = new ArrayList<>();
        for(ConvertTable tbl: ddlConfig.getTbls()){
            DdlObj vo = convert(tbl,ddlConfig.getDatabaseType(), ddlConfig.getConverFlag(), ddlConfig.getConvertMap());
            if(vo!=null){
                list.add(FreemarkerTool.processString(cfg, vo, ddlConfig.getTemplatePath()));
            }
        }
        return list;
    }
    public static DdlObj convert(ConvertTable tbl,String databaseType,boolean converFlag) throws Exception{
        return convert(tbl, databaseType, converFlag, null);
    }
    public static DdlObj convert(ConvertTable tbl,String databaseType,boolean converFlag,Map<String,String> diyConvertMap) throws Exception{
        if(tbl==null){
            return null;
        }
        if(StringUtils.isBlank(databaseType)){
            databaseType="";
        }
        
        tbl.setTableComment(escapeSQL(tbl.getTableComment()));
        for(ConvertColumn colx: tbl.getAllcols()){
            if(converFlag && diyConvertMap!=null && !diyConvertMap.isEmpty()){
                String columnDatatype=getByDbDataType(colx.getColumnDatatype(),diyConvertMap);
                if(StringUtils.isNotBlank(colx.getColumnLength())){
                    if(columnDatatype.contains("()")){
                        columnDatatype=columnDatatype.replaceFirst("\\(\\)", "("+colx.getColumnLength()+")");
                    }else{
                        columnDatatype=columnDatatype.replaceFirst("%s", colx.getColumnLength());
                    }
                }
                if(StringUtils.isNotBlank(colx.getColumnPrecision())){
                    if(columnDatatype.contains("()")){
                        columnDatatype=columnDatatype.replaceFirst("\\(\\)", "("+colx.getColumnPrecision()+")");
                    }else{
                        columnDatatype=columnDatatype.replaceFirst("%s", colx.getColumnPrecision());
                    }
                }
                colx.setColumnDatatype(columnDatatype);
            }
            colx.setColumnComment(escapeSQL(colx.getColumnComment()));
        }
        
        String tblcommenttype = tableHandler(databaseType, keyMap);
        String keytype = tableHandler(databaseType, tblCommentMap);
        String commenttype = tableHandler(databaseType, colCommentMap);
        
        DdlObj rootMap = new DdlObj();
        rootMap.setTable(tbl);
        rootMap.setDatabaseType(databaseType);
        rootMap.setKeyType(keytype);
        rootMap.setTblCommentType(tblcommenttype);
        rootMap.setColCommentType(commenttype);
        
        return rootMap;
    }
    /**
     * 获取数据库对应类型
     * @param databaseType
     * @param map
     * @return
     */
    public static String tableHandler(String databaseType,Map<String, List<String>> map) {
        if(StringUtils.isBlank(databaseType)){
            return "";
        }
        for (Entry<String, List<String>> entrySet : map.entrySet()) {
           if(entrySet.getValue().contains(databaseType)){
               return entrySet.getKey();
           }
        }
        return "";
    }
    public static String getByDbDataType(String columnDataType,Map<String, String> convertMap2) {
        if(StringUtils.isNotBlank(columnDataType)){
            if(convertMap2!=null && !convertMap2.isEmpty()){
                for (Entry<String, String> entrySet : convertMap2.entrySet()) {
                    if("--".equals(entrySet.getKey())){
                        continue;
                    }
                    Pattern p = Pattern.compile(entrySet.getKey(),Pattern.CASE_INSENSITIVE);
                    Matcher m = p.matcher(columnDataType);
                    if (m.find()) {
                        return entrySet.getValue();
                    }
                }
            }
            for (Entry<String, String> entrySet : convertMap.entrySet()) {
                Pattern p = Pattern.compile("^"+entrySet.getKey(),Pattern.CASE_INSENSITIVE);
                if("--".equals(entrySet.getKey())){
                    continue;
                }
                Matcher m = p.matcher(columnDataType);
                if (m.find()) {
                    return entrySet.getValue();
                }
            }
        }
        if(convertMap2.get("--")!=null){
            return convertMap2.get("--");
        }
        return convertMap.get("--");
    }
    public static String escapeSQL(String val){
        if(StringUtils.isBlank(val)){
            return null;
        }
        if(!val.contains("'")){
            return val;
        }
        val=val.trim().replaceAll("'", "''");
        return val;
    }
    public static void main(String[] args) throws Exception {
        ReaderSql r=new ReaderSql();
        ConvertConfig configParam=new ConvertConfig();
        configParam.setSqlPath("F:\\zl-work\\test.txt");
        r.parseData(configParam);
        DdlObj datas = DdlUtil.convert(configParam.getTbls().get(0), "derby", true, null);
    }
}
